package com.xb;


import com.xb.Entity.Customers;
import com.xb.util.dbUtil;
import org.junit.Test;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Properties;

public class PreparedStatementTest {
    //增加
    @Test
    public void test() throws Exception{
        InputStream resourceAsStream = ClassLoader.getSystemClassLoader().getResourceAsStream("mysqlConnectInfo.properties");
        Properties properties = new Properties();
        properties.load(resourceAsStream);
        String driver = properties.getProperty("driver");
        String user = properties.getProperty("user");
        String password =  properties.getProperty("password");
        String url =  properties.getProperty("url");
        //通过反射加载驱动
        Class.forName(driver);
        Connection connection = DriverManager.getConnection(url, user, password);
        //占位符 预编译sql语句 返回PreparedStatement
        String sql = "insert into customers(name,email,birth) values(?,?,?)";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1,"刘德华");
        preparedStatement.setString(2,"xiaobai@163.com");
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
        Date parse = simpleDateFormat.parse("1999-10-15");
        long time = parse.getTime();
        preparedStatement.setDate(3,new java.sql.Date(time));
        boolean execute = preparedStatement.execute();
        System.out.println(execute);
        preparedStatement.close();
        connection.close();
    }
    //删除
    @Test
    public void deletePreparedStatementTest() throws Exception{
        Connection connection = dbUtil.getConnection();
        String sql = "DELETE FROM customers WHERE id = ?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1,19);
        preparedStatement.execute();
        dbUtil.closeConAndPs(connection,preparedStatement);
    }
    //更新
    @Test
    public void updatePreparedStatementTest() throws Exception{
        Connection connection = dbUtil.getConnection();
        String sql = "UPDATE customers SET name = ?,email = ? WHERE id = ?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1,"周润发");
        preparedStatement.setString(2,"zrf@126.com");
        preparedStatement.setInt(3,1);
        preparedStatement.execute();
        dbUtil.closeConAndPs(connection,preparedStatement);
    }
    //查询
    @Test
    public void selectPreparedStatementTest() throws Exception{
        Connection connection = dbUtil.getConnection();
        String sql = "select * from customers";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);

        preparedStatement.execute();
        dbUtil.closeConAndPs(connection,preparedStatement);
    }
    @Test
    public void updatePreparedStatementTest02() throws Exception{

        String sql = "UPDATE customers SET name = ?,email = ? WHERE id = ?";
        ArrayList<Object> arr = new ArrayList<>();
        arr.add("刘德华");
        arr.add("ldh@163.com");
        arr.add("2");
        dbUtil.DclSql(sql,arr);
    }
    @Test
    public void updatePreparedStatementTest03() throws Exception{
        String sql = "UPDATE customers SET name = ?,email = ? WHERE id = ?";
        dbUtil.DclSql02(sql,"王菲","wf@163.com","2");
    }
    //查询
    @Test
    public void selectSQl() throws Exception{
        String sql = "SELECT * FROM customers WHERE id in (?,?,?)";
        List<Customers> customers = dbUtil.selectGetData(sql, Customers.class, "1", "2", "3");
        for (Customers customer : customers) {
            System.out.println(customer);
        }
    }
}
